ajFIXMsgToJSON function

Available since AlchemyJ v5.0

Description

The ajFIXMsgToJSON function can convert the FIX message to JSON string. It only support SOH delimiter in the message. This is the example for FIX message.

8=FIX.4.2
9=168
35=8
34=1
49=TRADETECH
56=COMPASS
52=20240730-15:55:12.456
150=0
39=0
37=20240731-001
17=20240730155512-001
48=AAPL
22=8
54=1
1=ACC001
38=1000
14=0
151=1000
6=0
15=USD
10=219

Syntax

ajFIXMsgToJSON(message,[tag_as_key],[version],[run_condition],[run_by_function_point_only])

Argument Name	Argument Type	Description
message (required)	Range	Specify content of the FIX message from a range of cells. The range of cells can span multiple rows and columns, and the content in cells are concatenated into one string from left to right and top to bottom.
tag_as_key (optional)	Boolean	TRUE means use the tag field as the JSON key. FALSE means use the tag field name as the JSON key. The default value is FALSE.
version (optional)	String	Support version: FIX.4.2, FIX.4.3, FIX.4.4, FIX.5.0, FIX.5.0SP1, FIX.5.0SP2. The default value is FIX.5.0SP2.
run_condition (optional)	Boolean	The function will run when the value is TRUE. Otherwise will not run. The default value is TRUE.
run_by_function_point_only (optional)	Boolean	If it equals FALSE, the function can be executed through 'Excel Calculation' (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Preview Function Point. If it equals TRUE, the function can be executed with Preview Function Point (AlchemyJ ribbon \ Preview Function Point) only. The default value is TRUE.

The function will return:

1) Return Value: JSON String.

2) Return Type: Single Value / Multiple values (array formula).

Example

Make sure the AlchemyJ Function Proxy was started up when executing ajFIXMsgToJSON in the AlchemyJ workbook. You can start the proxy from More Tools - Run AlchemyJ Function Proxy.

We use the below FIX.5.0SP2 message as example.

8=FIX.5.0SP2
9=168
35=8
34=1
49=TRADETECH
56=COMPASS
52=20240730-15:55:12.456
150=0
39=0
37=20240731-001
17=20240730155512-001
48=AAPL
22=8
54=1
1=ACC001
38=1000
14=0
151=1000
6=0
15=USD
10=219

Example 1

When set the tag_as_key parameter value is FALSE. Will use the FIX message tag name as the JSON key.

=ajFIXMsgToJSON(B1,B2,B3,B4,B5)

Example 2

When set the tag_as_key parameter value is TRUE. Will use the FIX message tag as the JSON key.

=ajFIXMsgToJSON(B1,B2,B3,B4,B5)

Note: If you want to covert the JSON back to the FIX message you can follow below steps to implement that.

1. Can use ajJSONToTable Transfer the JSON data to table.

2. Then use ajJSONFromSchema and ajFIXJSONToMsg convert the JSON to FIX message.

Error Scenarios

It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).

Error Scenario
FIX message is empty.
FIX message delimiter is not SOH.
FIX message version is not support.
It is not a FIX message.
FIX message tag's data type or value is incorrect.
FIX message tag is not belong to this MsgType or version.
FIX message tag 10 value is incorrect.